using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Globalization;
using System.Reflection;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using DataBroker.Criteria;
using DataBroker.Dao;
using log4net;

namespace DataBroker.Dal
{
    /// <summary>
    /// The D2Bk class offers static helper methods for 
    /// database access. It is designed to get infer all the necessary
    /// information from a supplied Entity type. The main assumption is 
    /// that the database table name is the same as the Entity class name.
    /// </summary>
    /// <summary>
    /// <threadsafety static="true" instance="true"></threadsafety>
    /// </summary>
    /// <remarks>$Id$</remarks>
    public sealed class D2Bk : MarshalByRefObject, IDisposable
    {

        /// <summary>
        /// 
        /// </summary>
        /// <param name="source"></param>
        /// <param name="databaseName"></param>
        public delegate void ButtonEventHandler(object source, string databaseName);
        internal static ButtonEventHandler ButtonEvent;
        /// <summary>
        /// 
        /// </summary>
        public static event ButtonEventHandler InvalidUserOrPassword
        {
            add
            {
                ButtonEvent += value;
            }
            remove
            {
                ButtonEvent -= value;
            }
        }

        private static readonly ILog Log =
            LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);


        /// <summary>
        /// 
        /// </summary>
        /// <param name="message"></param>
        public static void LogError(string message)
        {
            Log.Error(string.Format("{0}: {1}", new StackTrace(0, true).GetFrame(1).GetMethod().Name, message));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="message"></param>
        /// <param name="args"></param>
        public static void LogError(string message, params object[] args)
        {
            Log.Error(string.Format("{0} : {1}", new StackTrace(0, true).GetFrame(1).GetMethod().Name, string.Format(message, args)));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="message"></param>
        public static void LogDebug(string message)
        {
            Log.Debug(string.Format("{0}: {1}", new StackTrace(0, true).GetFrame(1).GetMethod().Name, message));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="message"></param>
        /// <param name="args"></param>
        public static void LogDebug(string message, params object[] args)
        {
            Log.Debug(string.Format("{0} : {1}", new StackTrace(0, true).GetFrame(1).GetMethod().Name, string.Format(message, args)));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="message"></param>
        public static void LogWarn(string message)
        {
            Log.Warn(string.Format("{0}: {1}", new StackTrace(0, true).GetFrame(1).GetMethod().Name, message));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="message"></param>
        /// <param name="args"></param>
        public static void LogWarn(string message, params object[] args)
        {
            Log.Warn(string.Format("{0} : {1}", new StackTrace(0, true).GetFrame(1).GetMethod().Name, string.Format(message, args)));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="message"></param>
        public static void LogFatal(string message)
        {
            Log.Fatal(string.Format("{0}: {1}", new StackTrace(0, true).GetFrame(1).GetMethod().Name, message));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="message"></param>
        /// <param name="args"></param>
        public static void LogFatal(string message, params object[] args)
        {
            Log.Fatal(string.Format("{0} : {1}", new StackTrace(0, true).GetFrame(1).GetMethod().Name, string.Format(message, args)));
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="message"></param>
        public static void LogInfo(string message)
        {
            Log.Info(string.Format("{0}: {1}", new StackTrace(0, true).GetFrame(1).GetMethod().Name, message));
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="message"></param>
        /// <param name="args"></param>
        public static void LogInfo(string message, params object[] args)
        {
            Log.Info(string.Format("{0} : {1}", new StackTrace(0, true).GetFrame(1).GetMethod().Name, string.Format(message, args)));
        }

        /// <summary>
        /// 
        /// </summary>
        private const int MaxRows = 30000;
        [ThreadStatic]
        private static D2BkSqlParameters _D2BkSqlParameters;
        /// <summary>
        /// Get parameters for current process/database connection.
        /// </summary>
        /// <returns>Object with current parameters.</returns>
        internal static D2BkSqlParameters d2BkSqlParameters
        {
            get
            {
                if (_D2BkSqlParameters == null)
                    _D2BkSqlParameters = new D2BkSqlParameters();
                return _D2BkSqlParameters;
            }
        }

        /// <summary>
        /// Sets the Entity type used to generate D2Bk.
        /// </summary>
        /// <param name="valueObjectType"></param>
        public static void SetEntityType(Type valueObjectType)
        {
            d2BkSqlParameters.ValueObjectType = valueObjectType;
        }


        /// <summary>
        /// Writes an Entity object or an array of entity objects to the database.
        /// </summary>
        /// <param name="entities">The entities to write to the database.</param>
        /// <returns>Number of records affected.</returns>
        public static void Save(ArrayList entities)
        {
            if (entities == null)
                return;
            foreach (Entity entity in entities)
            {
                Save(entity);
                PropertyInfo[] pinfo = entity.GetType().GetProperties();
                foreach (PropertyInfo pi in pinfo)
                {
                    if (pi.PropertyType == typeof(EntityCollection))
                    {
                        Save((ArrayList)pi.GetValue(entity, null));
                    }
                }
            }
        }
        /// <summary>
        /// Writes an Entity object or an array of entity objects to the database.
        /// </summary>
        /// <param name="entities">The entities to write to the database.</param>
        /// <returns>Number of records affected.</returns>
        public static void Save(List<Entity> entities)
        {
            if (entities == null)
                return;
            foreach (Entity entity in entities)
            {
                Save(entity);
                PropertyInfo[] pinfo = entity.GetType().GetProperties();
                foreach (PropertyInfo pi in pinfo)
                {
                    if (pi.PropertyType == typeof(EntityCollection))
                    {
                        Save((List<Entity>)pi.GetValue(entity, null));
                    }
                }
            }
        }
        /// <summary>
        /// Writes the object to the database depending on its 
        /// current status. If the status is PersistentDirty,
        /// an update will be issued. If the status is PersistentNew,
        /// an insert will be issued. If the status is PersistendDeleted, a
        /// delete will be issued. If the status is PersistentNewDeleted or PersistentClean,
        /// nothing happens. A Hollow status will cause an exception.
        /// </summary>
        /// <param name="entity">Object to update</param>
        /// <exception cref="System.Exception">Thrown if entity status is not valid.</exception>
        /// <returns>Number of rows affected</returns>
        public static int Save(Entity entity)
        {
            if (entity == null)
                return -1;
            int RowsUpdated = 0;
            SetEntityType(entity.GetType());
            if (entity.GetStatus() == Entity.EntityStatusEnum.PersistentDirty)
            {
                foreach (PropertyInfo prop in entity.GetType().GetProperties())
                {
                    if (prop.PropertyType.IsSubclassOf(typeof(Entity)))
                    {
                        Entity inner = (Entity)prop.GetValue(entity, null);
                        if (inner != null)
                        {
                            SetEntityType(inner.GetType());
                            RowsUpdated += Save(inner);
                        }
                    }
                }
                SetEntityType(entity.GetType());
                RowsUpdated += Update(entity);
            }
            if (entity.GetStatus() == Entity.EntityStatusEnum.PersistentNew)
            {
                foreach (PropertyInfo prop in entity.GetType().GetProperties())
                {
                    if (prop.PropertyType.IsSubclassOf(typeof(Entity)))
                    {
                        Entity inner = (Entity)prop.GetValue(entity, null);
                        if (inner != null)
                        {
                            //if (entity.IsDeleted())
                            //    inner.SetDelete();
                            SetEntityType(inner.GetType());
                            RowsUpdated += Save(inner);
                        }
                    }
                }
                SetEntityType(entity.GetType());
                RowsUpdated += Insert(entity);
            }
            if (entity.GetStatus() == Entity.EntityStatusEnum.PersistentDeleted)
            {
                RowsUpdated += Delete(entity);
                foreach (PropertyInfo prop in entity.GetType().GetProperties())
                {
                    if (prop.PropertyType.IsSubclassOf(typeof(Entity)))
                    {
                        Entity inner = (Entity)prop.GetValue(entity, null);
                        if (inner != null)
                        {
                            if (entity.IsDeleted())
                                inner.SetDelete();
                            RowsUpdated += Save(inner);
                        }
                    }
                }
            }
            entity.SetPersistentClean();
            return RowsUpdated;
        }


        /// <summary>
        /// Writes the object to the database depending on its 
        /// current status. If the status is PersistentDirty,
        /// an update will be issued. If the status is PersistentNew,
        /// an insert will be issued. If the status is PersistendDeleted, a
        /// delete will be issued. If the status is PersistentNewDeleted or PersistentClean,
        /// nothing happens. A Hollow status will cause an exception.
        /// </summary>
        /// <param name="entity">Object to update</param>
        /// <exception cref="System.Exception">Thrown if entity status is not valid.</exception>
        /// <returns>Number of rows affected</returns>
        public static int SaveOne(Entity entity)
        {
            if (entity == null)
                return -1;
            int RowsUpdated = 0;
            SetEntityType(entity.GetType());
            if (entity.GetStatus() == Entity.EntityStatusEnum.PersistentDirty)
            {
                SetEntityType(entity.GetType());
                RowsUpdated += Update(entity);
            }
            if (entity.GetStatus() == Entity.EntityStatusEnum.PersistentNew)
            {
                SetEntityType(entity.GetType());
                RowsUpdated += Insert(entity);
            }
            if (entity.GetStatus() == Entity.EntityStatusEnum.PersistentDeleted)
            {
                RowsUpdated += Delete(entity);
            }
            if (entity.GetStatus() == Entity.EntityStatusEnum.PersistentNewDeleted)
            {
                return 0;
            }
            entity.SetPersistentClean();
            return RowsUpdated;
        }




        /// <summary>
        /// Generates an ArrayList of objects of the specified type 
        /// according to the Filter.
        /// </summary>
        /// <param name="sc">Search criteria for query.</param>
        /// <param name="valueObjectType">Type of objects to be returned</param>
        /// <returns>Array of objects (empty if none found).</returns>
        /// <remarks>The <c>valueObjectType</c> parameter can be either derived
        /// from <c>Entity</c> or from <c>DBEntityJoin</c>.</remarks>


        public static List<Entity> Fetch(Filter sc, Type valueObjectType)
        {
            if (sc == null || valueObjectType == null)
                return null;
            if (valueObjectType.IsSubclassOf(typeof(Entity)))
                //if (valueObjectType.BaseType.FullName.Equals("DataAccessLayer.Entity"))
            {
                Entity entity = (Entity)Activator.CreateInstance(valueObjectType);
                return Fetch(sc, entity);
            }
            return null;
        }

        /// <summary>
        /// Generates an ArrayList of objects of the specified type 
        /// according to the Filter.
        /// </summary>
        /// <param name="sc">Search criteria for query.</param>
        /// <param name="entity">Entity of class type to be returned.</param>
        /// <returns>Array of objects (empty if none found).</returns>
        /// <remarks>The <c>valueObjectType</c> parameter can be either derived
        /// from <c>Entity</c> or from <c>DBEntityJoin</c>.</remarks>
        public static List<Entity> Fetch(Filter sc, Entity entity)
        {
            return Fetch(sc, entity, "");
        }

        ///<summary>
        ///</summary>
        ///<param name="sc"></param>
        ///<param name="entity"></param>
        ///<param name="hint"></param>
        ///<returns></returns>
        ///<exception cref="SqlException"></exception>
        public static List<Entity> Fetch(Filter sc, Entity entity, string hint)
        {
            return (List<Entity>) ExecuteUOW(new D2BkUOW(typeof(D2Bk).GetMethod("FetchUOW"), new ICloneable[] { sc, entity, hint }));
        }

        /// <summary>
        /// Generates an ArrayList of objects of the specified type 
        /// according to the Filter.
        /// </summary>
        /// <param name="sc">Search criteria for query.</param>
        /// <param name="entity">Entity of class type to be returned.</param>
        /// <param name="hint"></param>
        /// <returns>Array of objects (empty if none found).</returns>
        /// <returns>Array of objects (empty if none found).</returns>
        /// <remarks>The <c>valueObjectType</c> parameter can be either derived
        /// from <c>Entity</c> or from <c>DBEntityJoin</c>.</remarks>
        public static List<Entity> FetchUOW(Filter sc, Entity entity, string hint)
        {
            d2BkSqlParameters.SetTheObject(entity);
            D2BkSql d2BkSql = new D2BkSql(entity);
            d2BkSqlParameters.CurrentField = 1;
            string sql = d2BkSql.GetLoadSQL(sc, entity, hint);
            return Load(sql, sc, entity);
        }

        ///<summary>
        ///</summary>
        ///<param name="storedProcedureName"></param>
        ///<param name="sc"></param>
        ///<param name="entity"></param>
        ///<returns></returns>
        public static List<Entity> Fetch (string storedProcedureName, Filter sc, Entity entity)
        {
            return (List<Entity>) ExecuteUOW(new D2BkUOW(typeof(D2Bk).GetMethod("FetchUOWSP"), new ICloneable[] { storedProcedureName, sc, entity }));
        }


        private static object ExecuteUOW (D2BkUOW d2BkUOW)
        {
            List<Entity> result = null;
            IsolationLevel level = IsolationLevel.Chaos;
            if (D2BkConnection.CommandObject != null)
            {
                IDbTransaction trans = D2BkConnection.CommandObject.Transaction;
                if (trans != null)
                {
                    level = trans.IsolationLevel;
                }
                else
                {
                    D2BkConnection.UnitOfWork = new List<D2BkUOW>();
                }
            }
            else
            {
                D2BkConnection.UnitOfWork = new List<D2BkUOW>();
            }
            D2BkConnection.UnitOfWork.Add(d2BkUOW);
            for (int i = 0; i < 10; i++)
            {
                try
                {
                    lock (D2BkConnection.UnitOfWork)
                    {
                        object newResult = null;
                        for (int uow = i == 0 ? D2BkConnection.UnitOfWork.Count - 1 : 0; uow < D2BkConnection.UnitOfWork.Count; uow++)
                        {
                            newResult = D2BkConnection.UnitOfWork[uow].Execute();
                        }
                        SqlException sqlex = newResult as SqlException;
                        if (sqlex != null)
                        {
                            if (sqlex.Class == 13 && sqlex.Number == 1205) //deadlock
                            {
                                Thread.Sleep(1000 * ((i + 1) * (i + 1)));
                                Log.WarnFormat("Deadlock at {0} retry # {1}", MethodBase.GetCurrentMethod().Name, i);
                                if (level != IsolationLevel.Chaos)
                                {
                                    Rollback(false);
                                    BeginTransaction(level);
                                }
                                continue;
                            }
                            throw sqlex;
                        }
                        if (newResult != null)
                            return newResult;
                        return null;
                    }
                }
                catch (SqlException sqlex)
                {
                    if (sqlex.Class == 13 && sqlex.Number == 1205) //deadlock
                    {
                        Thread.Sleep(1000 * ((i + 1) * (i + 1)));
                        Log.WarnFormat("Deadlock at {0} retry # {1}", MethodBase.GetCurrentMethod().Name, i);
                        if (level != IsolationLevel.Chaos)
                        {
                            Rollback(false);
                            BeginTransaction(level);
                        }
                    }
                    else
                    {
                        throw;
                    }
                }
                catch (Exception e)
                {
                    SqlException sqlex = e.InnerException as SqlException;
                    if (sqlex != null)
                    {
                        if (sqlex.Class == 13 && sqlex.Number == 1205) //deadlock
                        {
                            Thread.Sleep(1000 * ((i + 1) * (i + 1)));
                            Log.WarnFormat("Deadlock at {0} retry # {1}", MethodBase.GetCurrentMethod().Name, i);
                            if (level != IsolationLevel.Chaos)
                            {
                                Rollback(false);
                                BeginTransaction(level);
                            }
                        }
                        else
                        {
                            throw;
                        }
                    }
                    else
                    {
                        Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                        throw;
                    }
                }
            }
            return result;
        }

        ///<summary>
        ///</summary>
        ///<param name="storedProcedureName"></param>
        ///<param name="sc"></param>
        ///<param name="entity"></param>
        ///<returns></returns>
        public static List<Entity> FetchUOWSP(string storedProcedureName, Filter sc, Entity entity)
        {
            d2BkSqlParameters.SetTheObject(entity);
            d2BkSqlParameters.CurrentField = 1;
            bool isSP = !storedProcedureName.ToLowerInvariant().Contains(" ");
            if (!isSP)
            {
                List<string> projections = D2BkSql.GetPrecacheFieldList(entity);
                sc.AddProjection(projections);
                string where;
                D2BkSql sb = new D2BkSql(entity);
                where = sb.GetWhere(sc.Bindings());
                if (where.Length > 0)
                {
                    storedProcedureName = String.Format("{0} WHERE ( {1} )", storedProcedureName, where);
                }
                d2BkSqlParameters.CurrentField = 1;
            }
            List<Entity> result = Load(storedProcedureName, sc, entity, isSP);
            return result;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="sc"></param>
        /// <returns></returns>
        public static String GetWhere(Entity entity, Filter sc)
        {
            D2BkSql d2BkSql = new D2BkSql(entity);
            return d2BkSql.GetWhere(sc.Bindings());
        }


        #region Constructors
        /// <summary>
        /// Gets the current Entity type assigned.
        /// </summary>
        /// <exception cref="System.Exception">Thrown when no Entity type has 
        /// been set.</exception>
        /// <returns>The current Entity type.</returns>
        /// <summary>
        /// Default constructor.
        /// </summary>
        public D2Bk()
        {
            GetMaxRows();
        }

        /// <summary>
        /// Constructor that accepts an entity type.
        /// </summary>
        /// <param name="entityType">Entity type. Use the <c>typeof</c> 
        /// operator or <c>variable.GetType().</c></param>
        public D2Bk(Type entityType)
        {
            d2BkSqlParameters.ValueObjectType = entityType;
        }
        #endregion
        #region Maximum Rows and Paging
        private static long GetMaxRows()
        {
            if (d2BkSqlParameters.MaxRows == -1)
            {
                D2BkSettingsCollection settings = new D2BkSettingsCollection();
                string sMaxRows = settings["MaxRows"];
                d2BkSqlParameters.MaxRows = string.IsNullOrEmpty(sMaxRows) ? MaxRows : long.Parse(sMaxRows, CultureInfo.InvariantCulture);
            }
            return d2BkSqlParameters.MaxRows;
        }



        /// <summary>
        /// Property that uniquely defines the current user.
        /// </summary>
        public static int CurrentUser()
        {
            return d2BkSqlParameters.CurrentUser;
        }

        /// <summary>
        /// Property that uniquely defines the current user.
        /// </summary>
        public static void CurrentUser(int value)
        {
            d2BkSqlParameters.CurrentUser = value;
        }
        #endregion
        #region Retrieve methods
        /// <summary>
        /// Retrieve the record that matches columns marked as primary key.
        /// </summary>
        /// <param name="theObject">Entity object used as template from which properties
        /// will be used to create the Filter clause.</param>
        /// <returns>All instances of the entity in the database matching the
        /// values passed in the parameter. Empty ArrayList if not found.</returns>
        /// <example>
        /// Public Class CLAIM_HEADER Inherits AuditableEntity
        /// ...
        /// <code>&lt;IsPrimaryKey()&gt; Public Property NUM_SEQ_CLAIM() As Integer</code>
        /// Each column defined as primary key will be <c>And</c>'ed in a <c>WHERE</c> clause. 
        ///</example>
        public static ArrayList Fetch(Entity theObject)
        {
            if (theObject == null)
                return null;
            SetEntityType(theObject.GetType());
            Filter sc = new Filter(D2BkSql.GetPrecacheFields(theObject.GetType()));
            ArrayList result;
            foreach (PropertyInfo prop in GetEntityType().GetProperties())
            {
                if (prop.GetValue(theObject, null) != null)
                    sc.Add(prop.Name, prop.GetValue(theObject, null));
            }

            try
            {
                result = Fetch(sc);
            }
            catch (Exception e)
            {
                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                throw;
            }
            return result;
        } // END OF loadByPK 



        /// <summary>
        /// Finds an Entity in a collection, based on a DataReader information. Used in joins
        /// to create the hierarchical structure. 
        /// </summary>
        /// <param name="tal">Entity collection to search</param>
        /// <param name="reader">Data reader with data to search for</param>
        /// <returns></returns>
        /// <remarks>This is a somewhat slow method, and it can be improved by using primary key if available.</remarks>
        private static object FindEntity(IList<Entity> tal, IDataRecord reader)
        {
            if (tal != null)
            {
                if (tal.Count == 0)
                {
                    return null;
                }
                for (int i = 0; i < tal.Count; i++)
                {
                    Entity entity = null;
                    if (tal[i].GetType().IsSubclassOf(typeof(Entity)))
                    {
                        entity = tal[i];
                    }
                    PropertyInfo[] properties = entity.GetType().GetProperties();
                    bool Equal = true;
                    for (int j = 0; j < properties.Length; j++)
                    {
                        try
                        {
                            object obj2 = properties[j].GetValue(entity, null);
                            if (!(obj2 is EntityCollection) && !(obj2 is Entity))
                            {
                                object obj3 = reader[entity.TableAlias() + properties[j].Name];
                                if ((obj2 == null) && (obj3 != DBNull.Value))
                                {
                                    Equal = false;
                                    break;
                                }
                                if ((obj2 != null) && (obj3 == DBNull.Value))
                                {
                                    Equal = false;
                                    break;
                                }
                                if ((obj2 != null) && (obj3 != DBNull.Value))
                                {
                                    if (IsColumnPrimaryKey(properties[j]))
                                    {
                                        Equal = obj2.Equals(obj3);
                                        break;
                                    }
                                    string s = obj2 as string;
                                    if (s != null)
                                    {
                                        Type entityPropertyType = GetEntityPropertyType(properties[j]);
                                        if ((entityPropertyType == typeof(string)) && !obj2.Equals(obj3))
                                        {
                                            Equal = false;
                                            break;
                                        }
                                        if ((entityPropertyType == typeof(int)) && !int.Parse(s, CultureInfo.InvariantCulture).Equals(obj3))
                                        {
                                            Equal = false;
                                            break;
                                        }
                                        if ((entityPropertyType == typeof(DateTime)) && !DateTime.Parse(s, CultureInfo.InvariantCulture).Equals(obj3))
                                        {
                                            Equal = false;
                                            break;
                                        }
                                        if ((entityPropertyType == typeof(bool)) && !bool.Parse(s).Equals(obj3))
                                        {
                                            Equal = false;
                                            break;
                                        }
                                    }
                                    else if (!obj2.Equals(obj3))
                                    {
                                        Equal = false;
                                        break;
                                    }
                                    if (IsColumnPrimaryKey(properties[j]))
                                    {
                                        break;
                                    }
                                }
                            }
                        }
                        catch (IndexOutOfRangeException)
                        {
                            Equal = false;
                        }
                    }
                    if (Equal)
                    {
                        return tal[i];
                    }
                }
            }
            return null;
        }


        /// <summary>
        /// Converts a value from one type to another
        /// </summary>
        /// <param name="theValue">Value to convert</param>
        /// <param name="valueType">Type to convert to</param>
        /// <returns>Converted value</returns>
        public static object Convert(object theValue, Type valueType)
        {
            object assignedValue = theValue;
            if (valueType == typeof(Boolean))
            {
                string strValue = "False";
                if (theValue != null)
                {
                    switch (theValue.ToString().ToLower())
                    {
                        case "1":
                            strValue = "true";
                            break;
                        case "0":
                            strValue = "false";
                            break;
                        case "f":
                            strValue = "false";
                            break;
                        case "t":
                            strValue = "true";
                            break;
                        case "n":
                            strValue = "false";
                            break;
                        case "y":
                            strValue = "true";
                            break;
                        case "false":
                            strValue = "false";
                            break;
                        case "true":
                            strValue = "true";
                            break;
                        default:
                            strValue = "false";
                            break;
                    }
                }
                assignedValue = System.Convert.ToBoolean(strValue, CultureInfo.InvariantCulture);
            }
            else
                if (valueType == typeof(byte[]))
                {
                    assignedValue = System.Convert.ToByte(theValue, CultureInfo.InvariantCulture);
                }
                else
                    if (valueType == typeof(Byte))
                    {
                        assignedValue = System.Convert.ToByte(theValue, CultureInfo.InvariantCulture);
                    }
                    else
                        if (valueType == typeof(Char))
                        {
                            assignedValue = System.Convert.ToChar(theValue, CultureInfo.InvariantCulture);
                        }
                        else
                            if (valueType == typeof(DateTime))
                            {
                                assignedValue = System.Convert.ToDateTime(theValue, CultureInfo.InvariantCulture);
                            }
                            else
                                if (valueType == typeof(Decimal))
                                {
                                    assignedValue = System.Convert.ToDecimal(theValue, CultureInfo.InvariantCulture);
                                }
                                else
                                    if (valueType == typeof(Double))
                                    {
                                        assignedValue = System.Convert.ToDouble(theValue, CultureInfo.InvariantCulture);
                                    }
                                    else
                                        if (valueType == typeof(Int16))
                                        {
                                            assignedValue = System.Convert.ToInt16(theValue, CultureInfo.InvariantCulture);
                                        }
                                        else
                                            if (valueType == typeof(Int32))
                                            {
                                                assignedValue = System.Convert.ToInt32(theValue, CultureInfo.InvariantCulture);
                                            }
                                            else
                                                if (valueType == typeof(Int64))
                                                {
                                                    assignedValue = System.Convert.ToInt64(theValue, CultureInfo.InvariantCulture);
                                                }
                                                else
                                                    if (valueType == typeof(SByte))
                                                    {
                                                        assignedValue = System.Convert.ToSByte(theValue, CultureInfo.InvariantCulture);
                                                    }
                                                    else
                                                        if (valueType == typeof(Single))
                                                        {
                                                            assignedValue = System.Convert.ToSingle(theValue, CultureInfo.InvariantCulture);
                                                        }
                                                        else
                                                            if (valueType == typeof(String))
                                                            {
                                                                assignedValue = System.Convert.ToString(theValue, CultureInfo.InvariantCulture);
                                                            }
                                                            else
                                                                if (valueType == typeof(UInt16))
                                                                {
                                                                    assignedValue = System.Convert.ToUInt16(theValue, CultureInfo.InvariantCulture);
                                                                }
                                                                else
                                                                    if (valueType == typeof(UInt32))
                                                                    {
                                                                        assignedValue = System.Convert.ToUInt32(theValue, CultureInfo.InvariantCulture);
                                                                    }
                                                                    else
                                                                        if (valueType == typeof(UInt64))
                                                                        {
                                                                            assignedValue = System.Convert.ToUInt64(theValue, CultureInfo.InvariantCulture);
                                                                        }
            return assignedValue;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="prop"></param>
        /// <returns></returns>

        public static string GetPropertyFormat(PropertyInfo prop)
        {
            object[] classAttributes = prop.GetCustomAttributes(typeof(DisplayFormatAttribute), false);
            if (classAttributes.GetLength(0) != 0)
            {
                return ((DisplayFormatAttribute)classAttributes[0]).Format;
            }
            Type propertyType = PropertyHelper.GetPropertyAttributeType(prop);
            string format;
            if (propertyType == typeof(DateTime))
            {
                format = D2BkSettingsCollection.GetConfigKey("dateFormat");
                if (!string.IsNullOrEmpty(format))
                    return format;
                return "G";
            }
            if (propertyType == typeof(Single))
            {
                format = D2BkSettingsCollection.GetConfigKey("singleFormat");
                if (!string.IsNullOrEmpty(format))
                    return format;
                return "g";
            }
            if (propertyType == typeof(Decimal))
            {
                format = D2BkSettingsCollection.GetConfigKey("decimalFormat");
                if (!string.IsNullOrEmpty(format))
                    return format;
                return "g";
            }
            if (propertyType == typeof(Double))
            {
                format = D2BkSettingsCollection.GetConfigKey("doubleFormat");
                if (!string.IsNullOrEmpty(format))
                    return format;
                return "g";
            }
            return "";
        }


        private static void SetEntityPropertyValue(Entity entity, PropertyInfo prop, IDataRecord reader, string columnAlias)
        {
            int ordinal = reader.GetOrdinal(columnAlias);
            if (reader.IsDBNull(ordinal))
            {
                return;
            }

            Type valueType = reader.GetFieldType(ordinal);

            object theValue;

            if (valueType == typeof(string))
            {
                theValue = reader.GetString(ordinal);
            }
            else if (valueType == typeof(DateTime))
            {
                try
                {
                    theValue = reader.GetDateTime(ordinal);
                }
                catch
                {
                    try
                    {
                        theValue = DateTime.ParseExact(reader.GetString(ordinal), GetPropertyFormat(prop), CultureInfo.InvariantCulture);
                    }
                    catch
                    {
                        string[] value = reader.GetString(ordinal).Split(' ');
                        string[] datepart = value[0].Split('/');
                        if (value.Length > 1)
                        {
                            string[] timepart = value[1].Split(':');
                            theValue = value.Length > 2 ? string.Format("{0:D2}/{1:D2}/{2:D2} {3:D2}:{4:D2}:{5:D2} {6}",
                                                                        int.Parse(datepart[0]), int.Parse(datepart[1]), int.Parse(datepart[2]),
                                                                        int.Parse(timepart[0]), int.Parse(timepart[1]), int.Parse(timepart[2]),
                                                                        value[2]) :
                                                                                      string.Format("{0:D2}/{1:D2}/{2:D2} {3:D2}:{4:D2}:{5:D2}", int.Parse(datepart[0]),
                                                                                                    int.Parse(datepart[1]), int.Parse(datepart[2]), int.Parse(timepart[0]),
                                                                                                    int.Parse(timepart[1]), int.Parse(timepart[2]));
                        }
                        else
                        {
                            theValue =
                                string.Format("{0:D2}/{1:D2}/{2:D2} 00:00:00 AM", int.Parse(datepart[0]),
                                              int.Parse(datepart[1]), int.Parse(datepart[2]));
                        }
                        theValue = DateTime.Parse(theValue.ToString());
                    }
                }
            }
            else if (valueType == typeof(Int32))
            {
                theValue = reader.GetInt32(ordinal);
            }
            else if (valueType == typeof(Int16))
            {
                theValue = reader.GetInt16(ordinal);
            }
            else if (valueType == typeof(bool))
            {
                theValue = reader.GetBoolean(ordinal);
            }
            else if (valueType == typeof(byte))
            {
                theValue = reader.GetByte(ordinal);
            }
            else if (valueType == typeof(byte[]))
            {
                long bytesize = reader.GetBytes(ordinal, 0, null, 0, 0);
                theValue = new byte[bytesize];
                long bytesread = 0;
                int curpos = 0;
                int chunkSize = (int)bytesize < 20480 ? (int)bytesize : 20480;
                while (bytesread < bytesize)
                {
                    // chunkSize is an arbitrary application defined value 
                    bytesread += reader.GetBytes(ordinal, curpos, (byte[])theValue, curpos, chunkSize);
                    curpos += chunkSize;
                }
            }
            else if (valueType == typeof(char))
            {
                theValue = reader.GetChar(ordinal);
            }
            else if (valueType == typeof(char[]))
            {
                long bytesize = reader.GetChars(ordinal, 0, null, 0, 0);
                theValue = new byte[bytesize];
                long bytesread = 0;
                int curpos = 0;
                int chunkSize = (int)bytesize < 20480 ? (int)bytesize : 20480;
                while (bytesread < bytesize)
                {
                    // chunkSize is an arbitrary application defined value 
                    bytesread += reader.GetChars(ordinal, curpos, (char[])theValue, curpos, chunkSize);
                    curpos += chunkSize;
                }
            }
            else if (valueType == typeof(decimal))
            {
                theValue = reader.GetDecimal(ordinal);
            }
            else if (valueType == typeof(double))
            {
                theValue = reader.GetDouble(ordinal);
            }
            else if (valueType == typeof(float))
            {
                theValue = reader.GetFloat(ordinal);
            }
            else if (valueType == typeof(Guid))
            {
                theValue = reader.GetGuid(ordinal);
            }
            else if (valueType == typeof(Int64))
            {
                theValue = reader.GetInt64(ordinal);
            }
            else
            {
                theValue = reader[columnAlias];
            }
            bool isNull = theValue is DBNull;
            if (IsColumnPrimaryKey(prop))
            {
                if (IsEmpty(theValue))
                {
                    Log.Debug(string.Format("Found empty PK at {0} {1}", MethodBase.GetCurrentMethod().Name, prop.Name));
                    entity.SetPersistentClean();
                }
            }

            if (!isNull)
            {
                Type propertyType = prop.PropertyType;
                Type typeAttribute = PropertyHelper.GetPropertyAttributeType(prop);
                object assignedValue;
                try
                {
                    //We'll assume the destination type is String for now
                    if (propertyType == typeof(string))
                    {
                        if (theValue is byte[])
                            throw new EntityException("Column " + prop.Name + " should be declared byte[]");
                        else
                            if (typeAttribute == typeof(DateTime))
                                assignedValue = ((DateTime)theValue).ToString(GetPropertyFormat(prop), CultureInfo.InvariantCulture);
                            else if (typeAttribute == typeof(Decimal))
                                assignedValue = ((Decimal)theValue).ToString(GetPropertyFormat(prop), CultureInfo.InvariantCulture);
                            else if (typeAttribute == typeof(Double))
                                assignedValue = ((Double)theValue).ToString(GetPropertyFormat(prop), CultureInfo.InvariantCulture);
                            else if (typeAttribute == typeof(Single))
                                assignedValue = ((Single)theValue).ToString(GetPropertyFormat(prop), CultureInfo.InvariantCulture);
                            else
                                assignedValue = theValue.ToString();
                    }
                    else if (propertyType != valueType)
                    {
                        assignedValue = Convert(theValue, propertyType);
                        //WarnTypeDifference(entity, prop, propertyType, valueType);
                    }
                    else
                        assignedValue = theValue;
                }
                catch (Exception e)
                {
                    Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                    throw;
                }
                try
                {
                    FieldInfo fi = entity.GetType().GetField("_" + prop.Name, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.IgnoreCase);
                    if (fi != null)
                    {
                        try
                        {
                            fi.SetValue(entity, assignedValue);
                        }
                        catch (FieldAccessException)
                        {
                            prop.SetValue(entity, assignedValue, null);
                        }
                    }
                    else
                    {
                        prop.SetValue(entity, assignedValue, null);
                    }
                }
                catch (Exception e)
                {
                    Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                    throw;
                }
            }
            return;
        }

        internal static Entity LoadEntity(Entity parent, IDataReader reader, string alias, List<string> projections, ArrayList fieldNames)
        {
            string typeContainedInCollectionAlias = alias;
            foreach (PropertyInfo info in parent.GetType().GetProperties())
            {
                Entity elementInstance;
                TypeContainedAttribute[] customAttributes;
                string str3;
                if (!parent.IsFlat())
                {
                    if (info.PropertyType == typeof(EntityCollection))
                    {
                        EntityCollection tal = (EntityCollection)info.GetValue(parent, null);
                        if (tal != null)
                        {
                            elementInstance = (Entity)FindEntity(tal, reader);
                            if (elementInstance == null)
                            {
                                elementInstance = (Entity)((Entity)tal.ElementInstance).CloneEntity();
                                tal.Add(elementInstance);
                            }
                            customAttributes = (TypeContainedAttribute[])info.GetCustomAttributes(typeof(TypeContainedAttribute), false);
                            if ((customAttributes.Length > 0) && (customAttributes[0] != null))
                            {
                                typeContainedInCollectionAlias = customAttributes[0].TypeContainedInCollectionAlias;
                            }
                            LoadEntity(elementInstance, reader, typeContainedInCollectionAlias, projections, fieldNames);
                        }
                    }
                    else if ((projections != null) && (projections.Count > 0))
                    {
                        foreach (string str2 in projections)
                        {
                            if (str2.IndexOf(info.Name, StringComparison.OrdinalIgnoreCase) < 0) continue;
                            if (fieldNames.BinarySearch(info.Name.ToUpper(CultureInfo.InvariantCulture)) >= 0)
                            {
                                SetEntityPropertyValue(parent, info, reader, info.Name);
                            }
                            else if (fieldNames.BinarySearch((alias + info.Name).ToUpper(CultureInfo.InvariantCulture)) >= 0)
                            {
                                SetEntityPropertyValue(parent, info, reader, alias + info.Name);
                            }
                        }
                    }
                    else
                    {
                        str3 = alias + info.Name;
                        if ((!IsNotInTable(info) || IsComputedColumn(info)) && (fieldNames.BinarySearch(str3.ToUpper(CultureInfo.InvariantCulture)) >= 0))
                        {
                            SetEntityPropertyValue(parent, info, reader, str3);
                        }
                    }
                }
                if (parent.IsFlat() && info.PropertyType.IsSubclassOf(typeof(Entity)))
                {
                    EntityCollection entitys2 = (EntityCollection)parent.GetType().GetProperty(info.Name.TrimEnd(new char[] { '_' })).GetValue(parent, null);
                    if (entitys2 != null)
                    {
                        elementInstance = entitys2.ElementInstance as Entity ??
                                          (Entity)Activator.CreateInstance(info.PropertyType);
                        info.SetValue(parent, elementInstance, null);
                        string name = info.PropertyType.Name;
                        customAttributes = (TypeContainedAttribute[])info.GetCustomAttributes(typeof(TypeContainedAttribute), false);
                        if ((customAttributes.Length > 0) && (customAttributes[0] != null))
                        {
                            name = customAttributes[0].TypeContainedInCollectionAlias;
                        }
                        else
                        {
                            PropertyInfo pi = parent.GetType().GetProperty(info.Name.TrimEnd(new char[] { '_' }));
                            customAttributes = (TypeContainedAttribute[])pi.GetCustomAttributes(typeof(TypeContainedAttribute), false);
                            if ((customAttributes.Length > 0) && (customAttributes[0] != null))
                            {
                                name = customAttributes[0].TypeContainedInCollectionAlias;
                            }
                        }
                        if (parent.GetType().Name != name)
                        {
                            LoadEntity(elementInstance, reader, name, projections, fieldNames);
                        }
                    }
                }
                else if (info.PropertyType != typeof(EntityCollection))
                {
                    if ((projections != null) && (projections.Count > 0))
                    {
                        foreach (string str2 in projections)
                        {
                            if (str2.IndexOf(info.Name, StringComparison.OrdinalIgnoreCase) >= 0)
                            {
                                if (fieldNames.BinarySearch(info.Name.ToUpper(CultureInfo.InvariantCulture)) >= 0)
                                {
                                    SetEntityPropertyValue(parent, info, reader, info.Name);
                                }
                                else if (fieldNames.BinarySearch((alias + info.Name).ToUpper(CultureInfo.InvariantCulture)) >= 0)
                                {
                                    SetEntityPropertyValue(parent, info, reader, alias + info.Name);
                                }
                            }
                        }
                    }
                    else
                    {
                        str3 = alias + info.Name;
                        if ((!IsNotInTable(info) || IsComputedColumn(info)) && (fieldNames.BinarySearch(str3.ToUpper(CultureInfo.InvariantCulture)) >= 0))
                        {
                            SetEntityPropertyValue(parent, info, reader, str3);
                        }
                    }
                }
            }
            if ((projections != null) && (projections.Count > 0))
            {
                parent.SetHollow();
                return parent;
            }
            parent.SetPersistentClean();
            return parent;
        }

        internal static EntityCollection Load(string sql, Filter sc, Entity parent)
        {
            return Load(sql, sc, parent, false);
        }

        ///<summary>
        ///</summary>
        ///<param name="readEntity"></param>
        public delegate bool ReadEntityEventHandler(Entity readEntity);

        [ThreadStatic]
        public static ReadEntityEventHandler ReadEntity;


        internal static EntityCollection Load(string sql, Filter sc, Entity parent, bool IsSp)
        {
            EntityCollection list = new EntityCollection(parent);
            IDataReader reader = null;
            try
            {
                D2BkConnection.Open();
                IEnumerator bindings = sc.Bindings();

                D2BkConnection.ClearParameters();
                D2BkConnection.SetCommandText(sql);
                Bind(1, bindings, IsSp);

                CommandBehavior cb = D2BkConnection.DBDataReaderCommandBehavior();

                reader = !IsSp ? D2BkConnection.ExecuteReader(cb) : D2BkConnection.ExecuteReader(CommandBehavior.KeyInfo, CommandType.StoredProcedure);
                long lRowsToRetrieve = -1;

                if (sc.RowsPerPage > 0 && sc.PageNumber <= 0)
                {
                    sc.PageNumber = 1;
                }

                if (sc.RowsPerPage > 0 && sc.PageNumber > 0)
                {
                    lRowsToRetrieve = (sc.PageNumber - 1) * sc.RowsPerPage;
                    while (lRowsToRetrieve > 0)
                    {
                        reader.Read();
                        lRowsToRetrieve--;
                    }
                    lRowsToRetrieve = sc.RowsPerPage > 0 ? sc.RowsPerPage : -1;
                }
                else
                {
                    if (sc.MaxRows > 0)
                        lRowsToRetrieve = sc.MaxRows;
                }
                ArrayList fieldList = null;
                    while (reader.Read() && (sc.RowsPerPage > 0 ? lRowsToRetrieve > 0 : true) && (sc.MaxRows > 0 ? lRowsToRetrieve > 0 : true))
                {
                    if (fieldList == null)
                    {
                        fieldList = new ArrayList();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            fieldList.Add(reader.GetName(i).ToUpper(CultureInfo.InvariantCulture));
                        }
                        fieldList.Sort();
                    }
                    lRowsToRetrieve--;
                    Entity theObject = null;
                    if (parent.HasJoinedEntities() && !parent.IsFlat())
                        theObject = (Entity)FindEntity(list, reader);
                    bool FoundObject = false;
                    if (theObject == null)
                    {
                        theObject = (Entity)parent.CloneJoin();
                    }
                    else
                    {
                        FoundObject = true;
                    }
                    LoadEntity(theObject, reader, IsSp ? "" : theObject.GetType().Name, sc.Projections(), fieldList);
                    if (!FoundObject)
                    {
                        theObject.Index = list.Count;
                        list.Add(theObject);
                        if (ReadEntity != null)
                        {
                            if (!ReadEntity(theObject))
                                break;
                        }
                    }
                }
                try
                {
                    D2BkConnection.CommandObject.Cancel();
                }
                catch (NotSupportedException ex)
                {
                    Log.Error(ex.Message, ex);
                }
                if (!IsSp)
                    reader.Close();
            }
            catch (ArgumentException e)
            {
                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
            }
            catch (Exception e)
            {
                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
            }
            finally
            {
                try
                {
                    if (reader != null)
                    {
                        if (!IsSp)
                        {
                            if (!reader.IsClosed)
                                reader.Close();
                            reader.Dispose();
                        }
                    }
                }
                catch (InvalidOperationException ioex)
                {
                    Log.Error(ioex.Message, ioex);
                }
                finally
                {
                    D2BkConnection.Close();
                }
            }
            return list;
        }


        /// <summary>
        /// Executes a search for objects, using a D2Bk statement passed as a parameter and
        /// meeting the criteria specified by a Filter parameter. 
        /// </summary>
        /// <param name="spName">Stored procedure name.</param>
        /// <param name="parameterValues">Stored procedure parameters.</param>
        /// <returns>ArrayList of entity instances. Empty if none.</returns>
        public static DataSet Fetch(string spName, params object[] parameterValues)
        {
            DataSet dataSet;
            //call the overload that takes a connection in place of the connection string
            try
            {
                D2BkConnection.Open();
                dataSet = D2BkConnection.ExecuteDataset(spName, parameterValues);
            }
            catch (Exception e)
            {
                WriteToEventLog(e.Message);
                throw;
            }
            finally
            {
                D2BkConnection.Close();
            }
            return dataSet;
        }

        /// <summary>
        /// Executes a search for objects, using a D2Bk statement passed as a parameter and
        /// meeting the criteria specified by a Filter parameter. 
        /// </summary>
        /// <param name="sql">D2Bk string.</param>
        /// <param name="sc">Search Criteria.</param>
        /// <returns>ArrayList of entity instances. Empty if none.</returns>
        private static ArrayList Fetch(Filter sc, string sql)
        {
            ArrayList list = new ArrayList();
            D2BkSql d2BkSql = new D2BkSql(GetEntityType());
            if (((int)GetCount(sc)) > d2BkSqlParameters.MaxRows && d2BkSqlParameters.MaxRows > 0)
            {
                Log.Info(String.Format(CultureInfo.InvariantCulture, "Query for {0} will return more than {1} records. Please narrow your search criteria", d2BkSql.GetPrimaryTable(), d2BkSqlParameters.MaxRows));
            }
            //call the overload that takes a connection in place of the connection string
            IDataReader reader = null;
            try
            {
                D2BkConnection.Open();
                IEnumerator bindings = sc.Bindings();

                D2BkConnection.ClearParameters();
                D2BkConnection.SetCommandText(sql);
                Bind(1, bindings);

                reader = D2BkConnection.ExecuteReader();

                long lRowsToRetrieve;

                if (sc.RowsPerPage > 0 && sc.PageNumber <=0)
                {
                    sc.PageNumber = 1;
                }

                if (sc.RowsPerPage > 0 && sc.PageNumber > 0)
                {
                    lRowsToRetrieve = (sc.PageNumber - 1) * sc.RowsPerPage;
                    while (lRowsToRetrieve > 0)
                    {
                        reader.Read();
                        lRowsToRetrieve--;
                    }
                }
                lRowsToRetrieve = sc.RowsPerPage > 0 ? sc.RowsPerPage : -1;
                int EntityIndex = 0;
                while (reader.Read() && (sc.RowsPerPage > 0 ? lRowsToRetrieve > 0 : true))
                {
                    lRowsToRetrieve--;
                    Entity entity = (Entity)Activator.CreateInstance(GetEntityType());
                    List<string> precacheFields;
                    if (sc.Projections() == null || sc.Projections().Count == 0)
                    {
                        precacheFields = d2BkSql.GetPrecacheFieldsList();
                    }
                    else
                    {
                        precacheFields = sc.Projections();
                    }
                    foreach (string propName in precacheFields)
                    {
                        try
                        {
                            PropertyInfo prop = GetEntityType().GetProperty(propName);
                            SetEntityPropertyValue(entity, prop, reader, prop.Name);
                        }
                        catch (Exception e)
                        {
                            Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                            throw;
                        }
                    }
                    entity.SetPersistentClean();
                    entity.Index = EntityIndex;
                    EntityIndex++;
                    list.Add(entity);
                }
                D2BkConnection.CommandObject.Cancel();
            }
            catch (Exception e)
            {
                WriteToEventLog(e.Message);
                throw;
            }
            finally
            {
                if (reader != null)
                {
                    if (!reader.IsClosed)
                        reader.Close();
                    reader.Dispose();
                }
                D2BkConnection.Close();
            }
            return list;
        }

        /// <summary>
        /// Executes a search for objects, meeting the criteria specified by a  
        /// Filter parameter. 
        /// The D2Bk statement is derived from the entity. 
        /// </summary>
        /// <param name="sc">Search criteria</param>
        /// <returns>ArrayList of entity instances. Empty ArrayList if none found.</returns>
        /// <example>
        /// The following code will load Claim Header records matching a certain submission reason
        /// and created between two dates:
        /// <code>
        ///     Dim sc As New Filter
        ///     sc.Add("CDE_SUBM_REASON", CDE_SUBM_REASON.SelectedValue)
        ///     sc.Add(Logical.And, "SYS_CREATED", Operator.GreaterEqual, Me.DTM_PROCESSED_1.Text)
        ///     sc.Add(Logical.And, "SYS_CREATED", Operator.LessEqual, Me.DTM_PROCESSED_2.Text + " 11:59:59 PM")
        ///     D2Bk.Load(objSC)
        ///     </code>
        /// </example>
        /// <remarks>Assumes type was already defined by constructor
        /// or by calling <c>SetEntityType</c></remarks>
        public static ArrayList Fetch(Filter sc)
        {
            D2BkSql d2BkSql = new D2BkSql(GetEntityType());
            d2BkSqlParameters.CurrentField = 1;
            string sql = d2BkSql.GetLoadSQL(sc, GetEntityType());
            //Log.Debug(sql.ToString());
            return Fetch(sc, sql);
        }

        // Create an md5 sum string of this string
        private static string GetMd5Sum(string str)
        {
            // First we need to convert the string into bytes, which
            // means using a text encoder.
            Encoder enc = Encoding.Unicode.GetEncoder();

            // Create a buffer large enough to hold the string
            byte[] unicodeText = new byte[str.Length * 2];
            enc.GetBytes(str.ToCharArray(), 0, str.Length, unicodeText, 0, true);

            // Now that we have a byte array we can ask the CSP to hash it
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] result = md5.ComputeHash(unicodeText);

            // Build the final string by converting each byte
            // into hex and appending it to a StringBuilder
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < result.Length; i++)
            {
                sb.Append(result[i].ToString("X2"));
            }
            //D2Bk.LogDebug(string.Format("D2Bk -> {0}",str));
            // And return it
            return sb.ToString();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sc"></param>
        /// <param name="e"></param>
        /// <returns></returns>
        public static string Hash(Filter sc, Entity e)
        {
            d2BkSqlParameters.SetTheObject(e);
            D2BkSql d2BkSql = new D2BkSql(GetEntityType());
            d2BkSqlParameters.CurrentField = 1;
            Filter scc = sc.Copy();
            string sql = d2BkSql.GetLoadSQL(scc, e, 1, "");
            string md5String = GetMd5Sum(string.Format("{0}:{1}", sql, BindToString(scc.Bindings())));
            //Log.Debug(string.Format("Hash for {0} = {1}", sql.ToString(), md5String));
            return md5String;
        }

        /// <summary>
        /// Performs sort in memory.
        /// </summary>
        /// <param name="entityArray">Array of entity objects to be sorted.</param>
        /// <param name="sortExpression">Sort expression</param>
        /// <returns>Sorted array of entity objects.</returns>
        public static ArrayList Sort(ArrayList entityArray, string sortExpression)
        {
            if (entityArray == null || sortExpression == null)
                return entityArray;
            DataTable dt = new DataTable();
            ArrayList list = new ArrayList();
            String sortExpressionOrder = "ASC";
            if (entityArray.Count < 2)
                return entityArray;
            foreach (PropertyInfo prop in (entityArray[0].GetType().GetProperties()))
            {
                dt.Columns.Add(new DataColumn(prop.Name, prop.PropertyType));
            }
            dt.Columns.Add(new DataColumn("IsThisRowDeletedFromTheDatabase", typeof(bool)));
            dt.Columns.Add(new DataColumn("IsThisRowSelected", typeof(bool)));
            foreach (Entity be in entityArray)
            {
                DataRow dr = dt.NewRow();
                foreach (PropertyInfo prop in (entityArray[0].GetType()).GetProperties())
                {
                    dr[prop.Name] = prop.GetValue(be, null);
                    if (prop.Name == sortExpression)
                    {
                    }
                }
                if (be.GetStatus() == Entity.EntityStatusEnum.PersistentClean ||
                    be.GetStatus() == Entity.EntityStatusEnum.PersistentDirty)
                {
                    dr["IsThisRowDeletedFromTheDatabase"] = true;
                }
                dt.Rows.Add(dr);
            }
            try
            {
                DataView dv = new DataView(dt);
                dv.Sort = sortExpression + " " + sortExpressionOrder;
                for (int i = 0; i < dv.Count; i++)
                {
                    DataRowView reader = dv[i];
                    Object theObject = Activator.CreateInstance(entityArray[0].GetType());
                    foreach (PropertyInfo prop in (entityArray[0].GetType()).GetProperties())
                    {
                        object theValue = reader[prop.Name];
                        if (!(theValue is DBNull))
                        {
                            try
                            {
                                if (prop.PropertyType == typeof(string))
                                {
                                    prop.SetValue(theObject, theValue, null);
                                }
                                else
                                {
                                    prop.SetValue(theObject, theValue, null);
                                }
                            }
                            catch
                            {
                                //Fail silently. Fetch only properties have no SET.
                                Log.Info("Exception at " + MethodBase.GetCurrentMethod().Name);
                                throw;
                            }
                        }
                    }
                    if (reader["IsThisRowDeletedFromTheDatabase"] != DBNull.Value && (bool)reader["IsThisRowDeletedFromTheDatabase"])
                    {
                        ((Entity)theObject).SetDelete();
                    }
                    list.Add(theObject);
                }
                return list;
            }
            catch
            {
                //in any problem, return original array.
                Log.Info("Exception at " + MethodBase.GetCurrentMethod().Name);
                throw;
            }
        }
        #endregion

        /// <summary>
        /// Returns number of rows that match search criteria.
        /// </summary>
        /// <param name="sc">Search criteria</param>
        /// <returns>Number of rows matching criteria.</returns>
        public static long GetCount(Filter sc)
        {
            D2BkSql d2BkSql = new D2BkSql(GetEntityType());
            string sql = d2BkSql.GetCountSQL(sc);
            object res = ExecuteScalar(sql, sc);
            if (res != null)
                return (int)res;
            return 0;
        }

        /// <summary>
        /// Returns number of rows that match search criteria.
        /// </summary>
        /// <param name="sc">Search criteria</param>
        /// <param name="entityType">Type to create D2Bk.</param>
        /// <returns>Number of rows matching criteria.</returns>
        public static int GetCount(Filter sc, Type entityType)
        {
            D2BkSql d2BkSql = new D2BkSql(entityType);
            string sql = d2BkSql.GetCountSQL(sc);
            object res = ExecuteScalar(sql, sc);
            if (res != null)
                return Decimal.ToInt32((Decimal)res);
            return 0;
        }

        /// <summary>
        /// Returns number of rows that match search criteria.
        /// </summary>
        /// <param name="sc">Search criteria</param>
        /// <param name="entity">Type to create D2Bk.</param>
        /// <returns>Number of rows matching criteria.</returns>
        public static long GetCount(Filter sc, Entity entity)
        {
            D2BkSql d2BkSql = new D2BkSql(entity);
            string sql = d2BkSql.GetCountSQL(sc);
            object res = ExecuteScalar(sql, sc);
            if (res != null)
                return (int)res;
            return 0;
        }

        /// <summary>
        /// Executes D2Bk function on rows matching search criteria.
        /// </summary>
        /// <param name="sqlFunction">Function name (such as MAX, MIN)</param>
        /// <param name="sc">Search criteria</param>
        /// <returns></returns>
        public static object GetFunction(string sqlFunction, Filter sc)
        {
            D2BkSql d2BkSql = new D2BkSql(GetEntityType());
            string sql = d2BkSql.GetFunctionSQL(sqlFunction, sc);
            return ExecuteScalar(sql, sc);
        }

        /// <summary>
        /// Executes D2Bk function on rows matching search criteria.
        /// </summary>
        /// <param name="sqlFunction">Function name (such as MAX, MIN)</param>
        /// <param name="sc">Search criteria</param>
        /// <param name="entity">Entity to serve as a template for the D2Bk (mainly to extract
        /// the table name).</param>
        /// <returns></returns>
        public static object GetFunction(string sqlFunction, Filter sc, Entity entity)
        {
            D2BkSql d2BkSql = new D2BkSql(entity);
            string sql = d2BkSql.GetFunctionSQL(sqlFunction, sc, entity);
            return ExecuteScalar(sql, sc);
        }


        /// <summary>
        /// Executes a D2Bk command that returns a scalar based on search criteria.
        /// </summary>
        /// <param name="sql">D2Bk string.</param>
        /// <param name="sc">Search criteria.</param>
        /// <returns></returns>
        private static object ExecuteScalar(string sql, Filter sc)
        {
            object objResult;
            //call the overload that takes a connection in place of the connection string
            try
            {
                D2BkConnection.Open();
                D2BkConnection.ClearParameters();
                IEnumerator bindings = sc.Bindings();
                D2BkConnection.SetCommandText(sql);
                Bind(1, bindings);
                objResult = D2BkConnection.ExecuteScalar();
            }
            catch (Exception e)
            {
                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                throw;
            }
            finally
            {
                D2BkConnection.Close();
            }
            d2BkSqlParameters.CurrentField = 1;
            return objResult;
        }

        /// <summary>
        /// Executes a D2Bk command that returns a scalar based on search criteria.
        /// </summary>
        /// <param name="sql">D2Bk string.</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql)
        {
            object objResult;
            //call the overload that takes a connection in place of the connection string
            try
            {
                D2BkConnection.Open();
                D2BkConnection.ClearParameters();
                D2BkConnection.SetCommandText(sql);
                objResult = D2BkConnection.ExecuteScalar();
            }
            catch (Exception e)
            {
                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                throw;
            }
            finally
            {
                D2BkConnection.Close();
            }
            d2BkSqlParameters.CurrentField = 1;
            if (objResult == null)
            {
                throw new D2BkException("Unrecoverable error during ExecuteScalar.");
            }
            return objResult;
        }

        /// <summary>
        /// Executes a D2Bk command that returns a scalar based on search criteria.
        /// </summary>
        /// <param name="sql">D2Bk string.</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql)
        {
            int result;
            //call the overload that takes a connection in place of the connection string
            try
            {
                D2BkConnection.Open();
                D2BkConnection.ClearParameters();
                D2BkConnection.SetCommandText(sql);
                result = D2BkConnection.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                throw;
            }
            finally
            {
                D2BkConnection.Close();
            }
            d2BkSqlParameters.CurrentField = 1;
            if (result == -1)
            {
                //throw new D2BkException("Unrecoverable error in ExecuteNonQuery command.");
            }
            return result;
        }


        /// <summary>
        /// Executes a D2Bk command that returns a scalar based on search criteria.
        /// </summary>
        /// <param name="sql">D2Bk string.</param>
        /// <param name="parameterValues">Parameters for D2Bk string.</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params object[] parameterValues)
        {
            object objResult;
            //call the overload that takes a connection in place of the connection string
            try
            {
                D2BkConnection.Open();
                D2BkConnection.ClearParameters();
                D2BkConnection.SetCommandText(sql);
                objResult = D2BkConnection.ExecuteScalar(sql, parameterValues);
            }
            catch (Exception e)
            {
                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                throw;
            }
            finally
            {
                D2BkConnection.Close();
            }
            return objResult;
        }

        /// <summary>
        /// Execute stored procedure
        /// </summary>
        /// <param name="spName">Stored procedure name</param>
        /// <param name="parameterValues">Values for parameters.</param>
        /// <returns></returns>
        public static int ExecuteSP(string spName, params object[] parameterValues)
        {
            int result;
            //call the overload that takes a connection in place of the connection string
            try
            {
                D2BkConnection.Open();
                D2BkConnection.ClearParameters();
                result = D2BkConnection.ExecuteSP(spName, parameterValues);
            }
            catch (Exception ex)
            {
                throw new D2BkException(string.Format(CultureInfo.CurrentCulture, "Unrecoverable error during Execute SP: {0}", ex.Message));
            }
            finally
            {
                D2BkConnection.Close();
            }
            return result;
        }


        /// <summary>
        /// Execute stored procedure
        /// </summary>
        /// <param name="spName">Stored procedure name</param>
        /// <param name="parameterValues">Values for parameters.</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSetFromSP(string spName, params object[] parameterValues)
        {
            DataSet result;
            //call the overload that takes a connection in place of the connection string
            try
            {
                D2BkConnection.Open();
                D2BkConnection.ClearParameters();
                result = D2BkConnection.ExecuteDataSetFromSP(spName, parameterValues);
            }
            catch (Exception e)
            {
                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                throw;
            }
            finally
            {
                D2BkConnection.Close();
            }
            return result;
        }

        /// <summary>
        /// Execute stored procedure without parameters.
        /// </summary>
        /// <param name="spName">Stored procedure name</param>
        /// <returns>Value returned by sp.</returns>
        public static int ExecuteSP(string spName)
        {
            return D2BkConnection.ExecuteSP(spName, null);
        }

        /// <summary>
        /// Execute Dynamic D2Bk with given parameters
        /// </summary>
        /// <param name="sql">D2Bk text./</param>
        /// <param name="parameterValues">Values for D2Bk (any number of parameters)</param>
        /// <returns>Value returned by D2Bk statement.</returns>
        /// <remarks>This method only accepts valid D2Bk. To call stored procedures, use the function <see see="ExecuteSP(string spName)"/>ExecuteSP.</remarks>
        public static int ExecuteDynamicSql(string sql, params object[] parameterValues)
        {
            int result;
            //call the overload that takes a connection in place of the connection string
            try
            {
                D2BkConnection.Open();
                D2BkConnection.ClearParameters();
                D2BkConnection.SetCommandText(sql);
                result = D2BkConnection.ExecuteDynamicSql(sql, parameterValues);
            }
            catch (Exception e)
            {
                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                throw;
            }
            finally
            {
                D2BkConnection.Close();
            }
            d2BkSqlParameters.CurrentField = 1;
            if (result == -1)
            {
                throw new D2BkException("Unrecoverable error during ExecuteDynamicSQL.");
            }
            return result;
        }


        /// <summary>
        /// Execute dynamic D2Bk with given parameters
        /// </summary>
        /// <param name="sql">D2Bk text./</param>
        /// <param name="parameterValues">Values for D2Bk parameters - any number of parameters.</param>
        /// <returns>Value returned by Stored Procedure or Dynamic D2Bk.</returns>
        /// <example>
        /// <code>DataSet res = (DataSet) D2Bk.ExecuteDataSet("select * from Customers where country = @country ", "USA");
        /// </code></example>
        /// <remarks>The D2Bk command can be either a SELECT statement or a Stored Procedure name.</remarks>
        public static DataSet ExecuteDataSet(string sql, params object[] parameterValues)
        {
            DataSet result;
            //call the overload that takes a connection in place of the connection string
            try
            {
                D2BkConnection.Open();
                D2BkConnection.ClearParameters();
                D2BkConnection.SetCommandText(sql);
                result = D2BkConnection.ExecuteDataset(sql, parameterValues);
            }
            catch (Exception e)
            {
                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                throw;
            }
            finally
            {
                D2BkConnection.Close();
            }
            d2BkSqlParameters.CurrentField = 1;
            if (result == null)
            {
                throw new D2BkException("Unrecoverable error during ExecuteDataSet");
            }
            return result;
        }


        /// <summary>
        /// Execute dynamic D2Bk with given parameters
        /// </summary>
        /// <param name="sql">D2Bk text./</param>
        /// <returns>Value returned by Stored Procedure.</returns>
        public static object ExecuteDynamicSql(string sql)
        {
            return ExecuteDynamicSql(sql, null);
        }

        ///<summary>
        ///</summary>
        ///<param name="theObject"></param>
        ///<returns></returns>
        public static int Insert(Entity theObject)
        {
            return (int)ExecuteUOW(new D2BkUOW(typeof(D2Bk).GetMethod("InsertUOW"), new ICloneable[] { theObject }));
        }
        /// <summary>
        /// Inserts an entity in the database.
        /// </summary>
        /// <param name="theObject">Object to insert</param>
        /// <returns>Number of records affected.</returns>
        public static int InsertUOW(Entity theObject)
        {
            lock (theObject)
            {
                int result = -1;
                if (theObject.GetStatus() == Entity.EntityStatusEnum.PersistentClean)
                {
                    string message = "Cannot insert Entity previously read from the database or with ID greater than 0";
                    Log.Info(message + " at " + MethodBase.GetCurrentMethod().Name);
                    throw new D2BkException(message);
                }
                ArrayList errors = theObject.IsValid();
                if (errors.Count > 0)
                {
                    StringBuilder sb = new StringBuilder();
                    foreach (string s in errors)
                    {
                        sb.Append(s);
                        sb.Append("\r\n");
                    }
                    string message = "Cannot insert into table " + theObject.TableName() +
                                     ". The following values are invalid:\r\n" + sb;
                    Log.Info(message + " at " + MethodBase.GetCurrentMethod().Name);
                    throw new D2BkException(message);
                }
                D2BkSql d2BkSql = new D2BkSql(theObject);
                int iResult = -1;
                string sql = d2BkSql.GetInsertSQL(theObject);
                if (string.IsNullOrEmpty(sql))
                {
                    return iResult;
                }
                try
                {
                    D2BkConnection.Open();
                    D2BkConnection.SetCommandText(sql);
                    D2BkConnection.ClearParameters();
                    PropertyInfo pk = null;
                    foreach (PropertyInfo prop in (theObject.GetType()).GetProperties())
                    {
                        if (!IsNotInTable(prop))
                        {
                            if (!IsColumnIdentity(prop))
                            {
                                string identityName = GetTableIdentityName(prop);
                                if (identityName != null && identityName.Length == 0)
                                {
                                    object objValue = prop.GetValue(theObject, null);
                                    if (!IsNull(objValue) &&
                                        objValue != null &&
                                        !IsColumnDisplayOnly(prop) &&
                                        !(IsColumnZeroIsNull(prop) && int.Parse(objValue.ToString()) == 0))
                                    {
                                        if (!HasSequenceName(prop) ||
                                            (HasSequenceName(prop) && int.Parse(objValue.ToString()) != 0))
                                            D2BkConnection.AddParameter(prop.Name,
                                                                        ConvertPropertyTypeForUpdate(prop,
                                                                                                     prop.GetValue(
                                                                                                         theObject, null)));
                                    }
                                }
                            }
                            else
                            {
                                pk = prop;
                            }
                        }
                    }
                    result = D2BkConnection.ExecuteNonQuery();
                    if (result != -1)
                    {
                        if (pk != null)
                        {
                            D2BkConnection.ClearParameters();
                            string sequenceName = GetSequenceName(pk);
                            int value = 0;
                            if (D2BkConnection.ProviderType().IndexOf("Oracle") >= 0)
                            {
                                if (sequenceName == "")
                                    sequenceName = string.Format("{1}{0}_SEQ.CURRVAL", theObject.TableName(),
                                                                 D2BkConnection.DBOwner());
                                value =
                                    (int)
                                    Convert(
                                        D2BkConnection.ExecuteScalar(string.Format("SELECT {0} FROM DUAL", sequenceName)),
                                        typeof(int));
                            }
                            else if (D2BkConnection.ProviderType().IndexOf("SqlClient") >= 0)
                            {
                                if (sequenceName == "")
                                    sequenceName = string.Format("IDENT_CURRENT('{0}{1}')", D2BkConnection.DBOwner(),
                                                                 theObject.TableName());
                                value =
                                    (int)
                                    Convert(D2BkConnection.ExecuteScalar(string.Format("SELECT {0}", sequenceName)),
                                            typeof(int));
                            }
                            try
                            {
                                FieldInfo fi = theObject.GetType().GetField("_" + pk.Name,
                                                                            BindingFlags.NonPublic |
                                                                            BindingFlags.Instance |
                                                                            BindingFlags.IgnoreCase);
                                if (fi != null)
                                {
                                    fi.SetValue(theObject, value);
                                }
                                else
                                {
                                    pk.SetValue(theObject, value, null);
                                }
                            }
                            catch (Exception e)
                            {
                                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                                throw;
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                    throw;
                }
                finally
                {
                    D2BkConnection.Close();
                }
                if (result == -1)
                {
                    throw new D2BkException("Unrecoverable error during insert.");
                }
                return result;
            }
        }





        #region Update methods


        /// <summary>
        /// Update rows in the database defined by the object passed as a parameter  
        /// and set all values present in this object to all rows matching the Filter. 
        /// </summary>
        /// <param name="entity">Entity object used as template with values to update.</param>
        /// <param name="sc">Search criteria.</param>
        /// <param name="changedProperties">Changed properties.</param>
        /// <param name="ignoreNulls">If true, will not update null values. If true, will 
        /// set to Null any null value in the entity.</param>
        /// <returns>Number of records affected.</returns>
        private static int Update(Entity entity, Filter sc, ArrayList changedProperties, bool ignoreNulls)
        {
            int result = -1;
            D2BkSql d2BkSql = new D2BkSql(entity);
            try
            {
                D2BkConnection.Open();
                string sql = d2BkSql.GetUpdateSQL(entity, sc, changedProperties);
                if (!string.IsNullOrEmpty(sql))
                {
                    D2BkConnection.SetCommandText(sql);
                }
                else
                {
                    return result;
                }


                IEnumerator bindings = sc.Bindings();

                D2BkConnection.ClearParameters();

                if (!sc.Bindings().MoveNext())
                {
                    if (sc.Projections() == null)
                    {
                        string message = "Error: cannot update the entire table";
                        Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name + " : " + message);
                        throw new D2BkException(message);
                    }
                    else
                    {
                        WriteToEventLog("updating entire " + d2BkSql.GetPrimaryTable() + " table");
                    }
                }
                if (sc.Projections() == null) // && !theObject.Validate())
                {
                    return -1;
                }
                int i = 1;
                foreach (ArrayList arrChanged in changedProperties)
                {
                    try
                    {
                        string propName = (string)arrChanged[0];
                        PropertyInfo prop = entity.GetType().GetProperty(propName);
                        if (!ignoreNulls || (!IsNull(prop.GetValue(entity, null)) && !IsColumnPrimaryKey(prop)))
                        {
                            if (!IsColumnPrimaryKey(prop) && !IsColumnDisplayOnly(prop) && !IsNotInTable(prop))
                            {
                                if (IsNull(prop.GetValue(entity, null)))
                                {
                                    D2BkConnection.AddParameter(prop.Name, DBNull.Value);
                                }
                                else
                                {
                                    D2BkConnection.AddParameter(prop.Name, arrChanged[2]);
                                }
                                i++;
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                        throw;
                    }
                }
                Bind(i, bindings);
                result = D2BkConnection.ExecuteNonQuery();
                entity.SetHollow();
            }
            catch (Exception e)
            {
                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                throw;
            }
            finally
            {
                D2BkConnection.Close();
            }
            if (result == -1)
            {
                throw new D2BkException("Unrecoverable error during update.");
            }
            return result;
        }


        /// <summary>
        /// Updates object using its primary key columns.
        /// </summary>
        /// <param name="theObject">Entity to udate.</param>
        /// <param name="columnsToUpdate"></param>
        /// <returns>Number of affected records.</returns>
        public static int Update(Entity theObject, string[] columnsToUpdate)
        {
            if (theObject == null)
                return -1;
            IsolationLevel level = IsolationLevel.Chaos;
            if (D2BkConnection.CommandObject != null)
            {
                IDbTransaction trans = D2BkConnection.CommandObject.Transaction;
                if (trans != null)
                {
                    level = trans.IsolationLevel;
                    D2BkConnection.UnitOfWork.Add(new D2BkUOW(MethodBase.GetCurrentMethod(), new List<ICloneable> { theObject }.ToArray()));
                }
            }
            SetEntityType(theObject.GetType());
            int iRecordsAffected = 0;
            Filter sc = new Filter();
            int iBindingsCount = 0;
            ArrayList arrChanged = theObject.GetChangedProperties(columnsToUpdate);
            //add where clause for properties that were changed
            foreach (PropertyInfo prop in GetEntityType().GetProperties())
            {
                if (IsColumnPrimaryKey(prop))
                {
                    sc.Add(prop.Name, prop.GetValue(theObject, null));
                    iBindingsCount++;
                }
            }
            if (iBindingsCount == 0)
            {
                throw new EntityException("Update - must specify a primary key.");
            }
            bool alreadyRan = false;
            for (int i = 0; i < 10; i++)
            {
                try
                {
                    if (!alreadyRan)
                    {
                        alreadyRan = true;
                        iRecordsAffected = Update(theObject, sc, arrChanged, false);
                    }
                    else
                    {
                        object newResult = null;
                        lock (D2BkConnection.UnitOfWork)
                        {
                            foreach (D2BkUOW uow in D2BkConnection.UnitOfWork)
                            {
                                newResult = uow.Execute();
                            }
                            if (newResult != null)
                                return (int)newResult;
                            return 0;
                        }
                    }
                    break;
                }
                catch (SqlException sqlex)
                {
                    if (sqlex.Class == 13 && sqlex.Number == 1205) //deadlock
                    {
                        Thread.Sleep(1000 * ((i + 1) * (i + 1)));
                        Log.WarnFormat("Deadlock at {0} retry # {1}", MethodBase.GetCurrentMethod().Name, i);
                        if (level != IsolationLevel.Chaos)
                        {
                            Rollback();
                            BeginTransaction(level);
                        }
                    }
                    else
                    {
                        throw;
                    }
                }
                catch (Exception e)
                {
                    Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                    throw;
                }
            }
            if (iRecordsAffected > 0)
            {
                theObject.SetPersistentClean();
            }
            return iRecordsAffected;
        } // END OF updateByPK


        ///<summary>
        ///</summary>
        ///<param name="theObject"></param>
        ///<returns></returns>
        public static int Update(Entity theObject)
        {
            return (int)ExecuteUOW(new D2BkUOW(typeof(D2Bk).GetMethod("UpdateUOW"), new ICloneable[] { theObject }));
        }

        /// <summary>
        /// Updates object using its primary key columns.
        /// </summary>
        /// <param name="theObject">Entity to udate.</param>
        /// <returns>Number of affected records.</returns>
        public static int UpdateUOW(Entity theObject)
        {
            if (theObject == null)
                return -1;
            if (theObject.GetStatus() != Entity.EntityStatusEnum.PersistentDirty)
            {
                string message = "Entity Object must have been read from database prior to update";
                Log.Info("Exception at " + MethodBase.GetCurrentMethod().Name);
                throw new D2BkException(message);
            }
            int iRecordsAffected;
            Filter sc = new Filter();
            int iBindingsCount = 0;
            Entity be = theObject.GetOriginal();
            ArrayList arrChanged = theObject.GetChangedProperties();
            //add where clause for properties that were changed
            foreach (PropertyInfo prop in theObject.GetType().GetProperties())
            {
                if (!IsColumnPrimaryKey(prop)) continue;
                try
                {
                    object getValue = prop.GetValue(be, null);
                    sc.Add(prop.Name, getValue);
                }
                catch (Exception ex)
                {
                    LogError(ex.Message);
                }
                iBindingsCount++;
            }
            if (iBindingsCount == 0)
            {
                foreach (PropertyInfo prop in theObject.GetType().GetProperties())
                {
                    if (IsNotInTable(prop))
                        continue;
                    if (prop.GetValue(be, null) == null)
                        sc.Add(prop.Name, Logical.And, Operator.IsNull);
                    else
                    {
                        object propValue = prop.GetValue(be, null);
                        long longValue;
                        if (long.TryParse(propValue.ToString(), out longValue))
                        {
                            if (longValue == 0)
                            {
                                Filter zeroOrNull = new Filter();
                                zeroOrNull.Add(prop.Name, propValue);
                                zeroOrNull.Add(prop.Name, Logical.Or, Operator.IsNull);
                                sc.AddNested(zeroOrNull);
                                continue;
                            }
                        }
                        sc.Add(prop.Name, prop.GetValue(be, null));
                    }
                }
            }
            iRecordsAffected = Update(theObject, sc, arrChanged, false);
            if (iRecordsAffected > 0)
            {
                theObject.SetPersistentClean();
            }
            return iRecordsAffected;
        } // END OF updateByPK
        #endregion

        #region Delete methods
        /// <summary>
        /// Deletes the object passed as a parameter, based on its primary key fields.
        /// </summary>
        /// <remarks>Creates a new database connection.</remarks>
        /// <param name="theObject">Entity to delete.</param>
        /// <returns>Number of affected records.</returns>
        public static int Delete(Entity theObject)
        {
            if (theObject == null)
                return -1;
            int iResult;
            SetEntityType(theObject.GetType());
            try
            {
                D2BkConnection.Open();
                Filter sc = new Filter(D2BkSql.GetPrecacheFields(theObject.GetType()));
                foreach (PropertyInfo prop in theObject.GetType().GetProperties())
                {
                    if (IsColumnPrimaryKey(prop))
                    {
                        sc.Add(prop.Name, prop.GetValue(theObject, null));
                    }
                }
                iResult = Delete(theObject, sc);
            }
            catch (Exception e)
            {
                Log.Error("Exception at " + MethodBase.GetCurrentMethod().Name, e);
                throw;
            }
            finally
            {
                D2BkConnection.Close();
            }
            return iResult;
        }
        /// <summary>
        /// Deletes the object passed as a parameter, based on search criteria.
        /// </summary>
        /// <param name="theObject"></param>
        /// <param name="sc">Search criteria</param>
        /// <returns></returns>
        public static int Delete(Entity theObject, Filter sc)
        {
            int result;
            D2BkSql d2BkSql = new D2BkSql(theObject);
            try
            {
                if (sc == null)
                {
                    sc = new Filter(d2BkSql.GetPrecacheFields());
                }
                ArrayList sql = d2BkSql.GetDeleteSQL(sc);
                D2BkConnection.Open();
                if (sql.Count == 1)
                {
                    D2BkConnection.SetCommandText(sql[0].ToString());
                }
                else
                {
                    throw new D2BkException("Error preparing D2Bk");
                }

                IEnumerator bindings = sc.Bindings();

                D2BkConnection.ClearParameters();
                if (!sc.Bindings().MoveNext())
                {
                    D2BkException ex = new D2BkException("Error: cannot delete the entire table");
                    Log.Error("Error at " + MethodBase.GetCurrentMethod().Name, ex);
                    throw ex;
                }
                Bind(1, bindings);
                result = D2BkConnection.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Error at " + MethodBase.GetCurrentMethod().Name, e);
                throw;
            }
            finally
            {
                D2BkConnection.Close();
            }
            if (result == -1)
            {
                throw new D2BkException("Unrecoverable error during delete.");
            }
            return result;
        }
        #endregion
        #region Methods common to Load, Insert, Update and Delete


        #endregion
        #region Helper methods

        private static void Bind(int ind, IEnumerator bindings)
        {
            Bind(ind, bindings, false);
        }

        /// <summary>
        /// Binds the search bindings to the statement in progress.
        /// </summary>
        /// <param name="ind">index</param>
        /// <param name="bindings">Bindings.</param>
        /// <returns></returns>
        /// <param name="isSp"></param>
        private static void Bind(int ind, IEnumerator bindings, bool isSp)
        {
            while (bindings.MoveNext())
            {
                Clause bdg = (Clause)bindings.Current;
                Object val = bdg.Value;
                if (!isSp)
                {
                    String field = bdg.Field;
                    if (val is Filter)
                    {
                        Filter sc = (Filter)val;
                        Bind(ind, sc.Bindings());
                    }
                    else
                    {
                        if (val != null && bdg.Operator != Operator.IsNull &&
                            bdg.Operator != Operator.IsNotNull &&
                            bdg.Operator != Operator.In &&
                            bdg.Operator != Operator.ORACLE_SEQUENCE &&
                            field != null)
                        {
                            D2BkConnection.AddParameter(
                                D2BkConnection.ParameterName("@FLD" +
                                                             d2BkSqlParameters.CurrentField.ToString(CultureInfo.InvariantCulture)),
                                val);
                            d2BkSqlParameters.CurrentField++;
                        }
                    }
                }
                else
                {
                    D2BkConnection.AddParameter(
                        D2BkConnection.ParameterName(bdg.Field), val);
                }
            }
            return;
        }

        /// <summary>
        /// Binds the search bindings to the statement in progress.
        /// </summary>
        /// <param name="bindings">Enumerator for bindings.</param>
        /// <param name="result">result.</param>
        /// <returns></returns>
        private static string BindToString(IEnumerator bindings, StringBuilder result)
        {
            while (bindings.MoveNext())
            {
                Clause bdg = (Clause)bindings.Current;
                Object val = bdg.Value;
                if (val is Filter)
                {
                    Filter sc = (Filter)val;
                    BindToString(sc.Bindings(), result);
                }
                else
                {
                    if (val != null && bdg.Operator != Operator.IsNull &&
                        bdg.Operator != Operator.IsNotNull &&
                        bdg.Operator != Operator.In)
                    {
                        result.Append(string.Format("FLD{0}{1}", result.Length.ToString(CultureInfo.InvariantCulture), val));
                    }
                }
            }
            return result.ToString();
        }

        internal static string BindToString(IEnumerator bindings)
        {
            StringBuilder res = new StringBuilder();
            return BindToString(bindings, res);
        }
        /// <summary>
        /// Returns Display Only status for column
        /// </summary>
        /// <param name="member">Member Info for column.</param>
        /// <returns>True: column is display only</returns>
        internal static bool IsColumnDisplayOnly(MemberInfo member)
        {
            foreach (object attribute in member.GetCustomAttributes(typeof(DisplayOnlyAttribute), true))
                return attribute is DisplayOnlyAttribute;
            return false;
        }



        /// <summary>
        /// Returns "in table" status for column
        /// </summary>
        /// <param name="member">Member Info for column.</param>
        /// <returns>True: column is in NOT in table</returns>
        internal static bool IsNotInTable(MemberInfo member)
        {
            string version = D2BkConnection.DBVersion();
            foreach (object attribute in member.GetCustomAttributes(typeof(NotInTableAttribute), true))
            {
                if (attribute is NotInTableAttribute)
                {
                    string fieldVersion = ((NotInTableAttribute)attribute).DatabaseVersion;
                    if (!string.IsNullOrEmpty(version) && !string.IsNullOrEmpty(fieldVersion))
                    {
                        if (string.Compare(fieldVersion, version, true) == 0)
                            return true;
                    }
                    else
                    {
                        return true;
                    }
                }
            }
            return false;
        }

        /// <summary>
        /// Returns "in table" status for column
        /// </summary>
        /// <param name="member">Member Info for column.</param>
        /// <returns>True: column is in NOT in table</returns>
        internal static bool IsComputedColumn(MemberInfo member)
        {
            foreach (object attribute in member.GetCustomAttributes(typeof(ComputedColumnAttribute), true))
            {
                if (attribute is ComputedColumnAttribute)
                {
                    return true;
                }
            }
            return false;
        }


        /// <summary>
        /// Returns identity status for column
        /// </summary>
        /// <param name="member">Member Info for column.</param>
        /// <returns>True: column is identity</returns>
        internal static bool IsColumnIdentity(MemberInfo member)
        {
            foreach (object attribute in member.GetCustomAttributes(typeof(IsIdentityAttribute), true))
            {
                if (attribute is IsIdentityAttribute)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// Returns identity status for column
        /// </summary>
        /// <param name="member">Member Info for column.</param>
        /// <returns>True: column is identity</returns>
        internal static bool IsColumnZeroIsNull(MemberInfo member)
        {
            foreach (object attribute in member.GetCustomAttributes(typeof(ZeroIsNullAttribute), true))
            {
                if (attribute is ZeroIsNullAttribute)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// Returns identity status for column
        /// </summary>
        /// <param name="member">Member Info for column.</param>
        /// <returns>True: column is identity</returns>
        internal static bool HasSequenceName(MemberInfo member)
        {
            foreach (object attribute in member.GetCustomAttributes(typeof(SequenceNameAttribute), true))
            {
                if (attribute is SequenceNameAttribute)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// Returns identity status for column
        /// </summary>
        /// <param name="member">Member Info for column.</param>
        /// <returns>True: column is identity</returns>
        internal static string GetSequenceName(MemberInfo member)
        {
            if (D2BkConnection.ProviderType().IndexOf("Oracle") >= 0)
            {
                foreach (object attribute in member.GetCustomAttributes(typeof(SequenceNameAttribute), true))
                {
                    return string.Format("{1}{0}_SEQ.CURRVAL", ((SequenceNameAttribute)attribute).SequenceName, D2BkConnection.DBOwner());
                }
            }
            else if (D2BkConnection.ProviderType().IndexOf("SqlClient") >= 0)
            {
                foreach (object attribute in member.GetCustomAttributes(typeof(SequenceNameAttribute), true))
                {
                    return string.Format("IDENT_CURRENT('{0}{1}')", D2BkConnection.DBOwner(), ((SequenceNameAttribute)attribute).SequenceName);
                }
            }
            return "";
        }


        /// <summary>
        /// Returns identity status for column
        /// </summary>
        /// <param name="member">Member Info for column.</param>
        /// <returns>True: column is identity</returns>
        internal static Type GetEntityPropertyType(MemberInfo member)
        {
            foreach (TypeAttribute attribute in member.GetCustomAttributes(typeof(TypeAttribute), true))
            {
                return attribute.TypeName;
            }
            return typeof(string);
        }


        /// <summary>
        /// Returns identity status for column
        /// </summary>
        /// <param name="member">Member Info for column.</param>
        /// <returns>True: column is identity</returns>
        internal static string GetTableIdentityName(MemberInfo member)
        {
            foreach (object attribute in member.GetCustomAttributes(typeof(TableNameIdentityAttribute), true))
            {
                return ((TableNameIdentityAttribute)attribute).TableName;
            }
            return "";
        }

        /// <summary>
        /// Returns identity status for column
        /// </summary>
        /// <param name="member">Member Info for column.</param>
        /// <returns>True: column is identity</returns>
        internal static string GetComputedColumn(MemberInfo member)
        {
            foreach (object attribute in member.GetCustomAttributes(typeof(ComputedColumnAttribute), true))
            {
                return ((ComputedColumnAttribute)attribute).ComputedColumn;
            }
            return "";
        }


        /// <summary>
        /// Applies the regular expression (if any) before updating.
        /// </summary>
        /// <param name="prop"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object ConvertPropertyTypeForUpdate(PropertyInfo prop, object value)
        {
            if (value != null)
            {
                if (value.GetType() == typeof(string))
                {
                    object[] classAttributes = prop.GetCustomAttributes(typeof(DisplayUnFormatAttribute), false);
                    if (classAttributes.GetLength(0) != 0)
                    {
                        Regex r = new Regex(((DisplayUnFormatAttribute)classAttributes[0]).Expression);
                        value = r.Replace(value.ToString(), "");
                    }
                }
                Type typeAttribute = PropertyHelper.GetPropertyAttributeType(prop);
                if (typeAttribute == typeof(string))
                {
                    return value.ToString();
                }
                if (typeAttribute == typeof(DateTime))
                {
                    if (value.GetType() == typeof(string))
                    {
                        if (value.ToString() == "")
                            return DateTime.MinValue;
                        return DateTime.Parse(value.ToString(), CultureInfo.InvariantCulture);
                    }
                }
                if (typeAttribute == typeof(Int32))
                {
                    if (value.GetType() == typeof(string))
                    {
                        if (value.ToString() == "")
                            return Int32.MinValue;
                        return Int32.Parse(value.ToString(), CultureInfo.InvariantCulture);
                    }
                }
                if (typeAttribute == typeof(Int16))
                {
                    if (value.GetType() == typeof(string))
                    {
                        if (value.ToString() == "")
                            return Int16.MinValue;
                        return Int16.Parse(value.ToString(), CultureInfo.InvariantCulture);
                    }
                }
                if (typeAttribute == typeof(Int64))
                {
                    if (value.GetType() == typeof(string))
                    {
                        if (value.ToString() == "")
                            return Int64.MinValue;
                        return Int64.Parse(value.ToString(), CultureInfo.InvariantCulture);
                    }
                }
                if (typeAttribute == typeof(Boolean))
                {
                    if (value.GetType() == typeof(string))
                    {
                        if (value.ToString() == "")
                            return false;
                        return Boolean.Parse(value.ToString());
                    }
                }
                if (typeAttribute == typeof(Decimal))
                {
                    if (value.GetType() == typeof(string))
                    {
                        if (value.ToString() == "")
                            return false;
                        return Decimal.Parse(value.ToString());
                    }
                }
            }
            return value;
        }


        /// <summary>
        /// Checks if column is primary key.
        /// </summary>
        /// <param name="member">Member Info</param>
        /// <returns>True if it is primary key; false otherwise.</returns>
        internal static bool IsColumnPrimaryKey(MemberInfo member)
        {
            foreach (object attribute in member.GetCustomAttributes(true))
            {
                if (attribute is IsPKAttribute)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// Verifies if an object has a value that is considered Null from the
        /// database standpoint. For DateTime, int and Double this includes MinValue.
        /// </summary>
        /// <param name="value">Value to verify.</param>
        /// <returns>True if null.</returns>
        internal static bool IsNull(object value)
        {
            if (value is DateTime)
                return (DateTime)value == DateTime.MinValue;
            if (value is int)
                return ((int)value == int.MinValue);
            if (value is Double)
                return ((double)value == double.MinValue);
            if (value is Guid)
                return ((Guid)value == Guid.Empty);

            return value == null;
        }

        /// <summary>
        /// Verifies if an object has a value that is considered Null from the
        /// database standpoint. For DateTime, int and Double this includes MinValue.
        /// </summary>
        /// <param name="value">Value to verify.</param>
        /// <returns>True if null.</returns>
        internal static bool IsEmpty(object value)
        {
            if (value is DateTime)
                return (DateTime)value == DateTime.MinValue;
            if (value is int)
                return ((int)value == 0);
            if (value is Double)
                return ((double)value == 0.00D);
            if (value is Guid)
                return ((Guid)value == Guid.Empty);

            return value == null;
        }

        /// <summary>
        /// Static helper function to begin a database transaction.
        /// </summary>
        public static void BeginTransaction()
        {
            D2BkConnection.BeginTransaction();
        }

        /// <summary>
        /// Static helper function to begin a database transaction.
        /// </summary>
        public static void BeginTransaction(IsolationLevel isolation)
        {
            D2BkConnection.BeginTransaction(isolation);
        }


        /// <summary>
        /// Static helper function to Commit a transaction initiated with BeginTransaction().
        /// </summary>
        public static void Commit()
        {
            D2BkConnection.Commit();
        }
        /// <summary>
        /// Static helper function to Rollback a transaction initiated with BeginTransaction().
        /// </summary>
        public static void Rollback()
        {
            D2BkConnection.Rollback();
        }

        /// <summary>
        /// Static helper function to Rollback a transaction initiated with BeginTransaction().
        /// </summary>
        public static void Rollback(bool clear)
        {
            D2BkConnection.Rollback(clear);
        }

        public static List<D2BkUOW> UnitOfWork
        {
            get
            {
                return D2BkConnection.UnitOfWork;
            }
        }


        /// <summary>
        ///  Get current connection to database.
        /// </summary>
        /// <returns></returns>
        public static IDbConnection GetConnection()
        {
            return D2BkConnection.CommandObject.Connection;
        }

        /// <summary>
        /// Utility function to return zero if value is null.
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int NullToZero(object value)
        {
            int iNumber = 0;
            if (value != DBNull.Value)
                iNumber = (int)(value);
            return iNumber;
        }


        /// <summary>
        /// Save entry to event Log.
        /// </summary>
        /// <param name="message"></param>
        private static void WriteToEventLog(string message)
        {
            Log.Info(message);
        }

        #endregion
        #region Abstract Methods


        /// <summary>
        /// Get current entity type.
        /// </summary>
        /// <returns></returns>
        /// <remarks>Return Type.GetType("TYPE_OF.OBJECT")</remarks>
        private static Type GetEntityType()
        {
            if (d2BkSqlParameters.ValueObjectType == null)
                throw new D2BkException("Value Object Type not set");
            return d2BkSqlParameters.ValueObjectType;
        }

        /// <summary>
        /// Sort entities in array according to sort order
        /// </summary>
        /// <param name="entities">Array of entities.</param>
        /// <param name="sort">Column to sort by.</param>
        /// <param name="sortDirection">Asc or desc.</param>
        /// <returns>ArrayList of sorted entitites or the same array if an error occur.</returns>
        public static ArrayList SortOrder(ArrayList entities, string sort, string sortDirection)
        {
            if (entities == null || sortDirection == null)
                return entities;
            DataTable dt = new DataTable();
            ArrayList list = new ArrayList();
            if (entities.Count < 2)
                return entities;
            foreach (PropertyInfo prop in (entities[0].GetType().GetProperties()))
            {
                dt.Columns.Add(new DataColumn(prop.Name, prop.PropertyType));
            }
            dt.Columns.Add(new DataColumn("IsThisRowDeletedFromTheDatabase", typeof(bool)));
            dt.Columns.Add(new DataColumn("IsThisRowSelected", typeof(bool)));
            foreach (Entity be in entities)
            {
                DataRow dr = dt.NewRow();
                foreach (PropertyInfo prop in (entities[0].GetType()).GetProperties())
                {
                    dr[prop.Name] = prop.GetValue(be, null);
                    if (prop.Name == sort)
                    {
                    }
                }
                if (be.GetStatus() == Entity.EntityStatusEnum.PersistentDeleted)
                {
                    dr["IsThisRowDeletedFromTheDatabase"] = true;
                }
                dt.Rows.Add(dr);
            }
            {
                DataView dv = new DataView(dt);
                dv.Sort = sort + " " + sortDirection;
                for (int i = 0; i < dv.Count; i++)
                {
                    DataRowView reader = dv[i];
                    Object theObject = Activator.CreateInstance(entities[0].GetType());
                    foreach (PropertyInfo prop in (entities[0].GetType()).GetProperties())
                    {
                        object theValue = reader[prop.Name];
                        if (!(theValue is DBNull))
                        {
                            try
                            {
                                if (prop.PropertyType == typeof(string))
                                {
                                    prop.SetValue(theObject, theValue, null);
                                }
                                else
                                {
                                    prop.SetValue(theObject, theValue, null);
                                }
                            }
                            catch
                            {
                                Log.Info("Exception at " + MethodBase.GetCurrentMethod().Name);
                                throw;
                            }
                        }
                    }
                    if (reader["IsThisRowDeletedFromTheDatabase"] != DBNull.Value && (bool)reader["IsThisRowDeletedFromTheDatabase"])
                    {
                        ((Entity)theObject).SetDelete();
                    }
                    if (reader["IsThisRowSelected"] != DBNull.Value && (bool)reader["IsThisRowSelected"])
                    {
                        ((Entity)theObject).SetPersistentClean();
                    }
                    ((Entity)theObject).SetPersistentClean();
                    list.Add(theObject);
                }
                return list;
            }
        }
        #endregion
        /// <summary>
        /// Changes database region.
        /// </summary>
        /// <param name="newDb"></param>
        /// <returns></returns>
        public static string ChangeDatabase(string newDb)
        {
            return D2BkSettingsCollection.ChangeDatabase(newDb);
        }
        /// <summary>
        /// Returns the current database
        /// </summary>
        /// <returns></returns>
        public static string GetCurrentDatabase()
        {
            return D2BkSettingsCollection.GetCurrentDatabase();
        }

        ///<summary>
        /// Returns db owher as specified in config file.
        ///</summary>
        static public string DBOwner
        {
            get
            {
                return D2BkConnection.DBOwner();
            }
        }
        #region IDisposable Members

        /// <summary>
        /// Dispose will clean up any pending connection.
        /// </summary>
        public void Dispose()
        {
            D2BkConnection.Rollback();
            Dispose(true);
        }

        private bool disposed;
        private void Dispose(bool disposing)
        {
            if (disposed) return;
            // Set the sentinel.
            disposed = true;

            // Suppress finalization of this disposed instance.
            if (disposing)
            {
                GC.SuppressFinalize(this);
            }
        }


        /// <summary>
        /// Finalizer implementation.
        /// </summary>
        ~D2Bk()
        {
            Dispose(false);
        }
        #endregion
    }
}